create database do_an
go
use do_an
go
create table nhanvien(
idnv varchar(10) primary key,
ten varchar(30) NOT NULL,
diachi varchar(50) not null,
sodienthoai varchar(20) not null,
matkhau varchar(20) not null,
chucvu varchar(30) NOT NULL,
ngayvao varchar(20)
)
go

create table tienluong (
	chucvu varchar(30),
	luong varchar(50)

)
go
create table khachhang(
idkh varchar(20) primary key,
ten varchar(30)not null,
sodienthoai varchar(20)not null,
diachi varchar(40)not null
)
go
create table dathang(
	iddathang nvarchar(20) primary key,
	idnv varchar(10) references nhanvien(idnv) on update cascade on delete cascade,
	idkh  varchar(20) references khachhang(idkh) on update cascade on delete cascade,
	ngaydat varchar(30) not null
)
go
create table loaisanpham(
	idloaisp varchar(10) primary key,
	ten nvarchar(30)not null
)
go
create table sanpham( 
	idsp varchar(20) primary key,
	ten varchar(30)not null,
	giatien varchar(50)  not null,
	soluong int not null,
	loai varchar(10)  references loaisanpham(idloaisp) on update cascade on delete cascade,

)
go
create table dondathang(
	iddathang nvarchar(20) references dathang(iddathang) on update cascade on delete cascade,
	idsp varchar(20) not null references sanpham(idsp) on update cascade on delete cascade,
	tensanpham varchar(30) not null,
	soluong int not null,
	giatien varchar(50) not null
)
go
create table thuchi(
	idnv varchar(10),
	ngay varchar(20),
	idsp varchar(20),
	tensp varchar(30),
	giatien varchar(50),
	soluong int,
	tongtien varchar(50)
)
go
create table nhaphang(
	idnhaphang varchar(30),
	idnv varchar(10),
	ngay varchar(30),
	nguonnhap varchar(30),
	idsp varchar(20),
	loaisp varchar(20),
	soluong varchar(30),
	giatien varchar(50),
	tongtien varchar(50),
	
	
)
go
select * from nhanvien;
select * from khachhang;
select * from loaisanpham;
select * from sanpham;
select * from dathang;
select * from dondathang;
select * from thuchi;
select * from nhaphang;
go
insert into nhanvien values ('huy','huy','thai binh','01689949474','123','Boss');
insert into nhanvien values ('kien','kien','thai binh','01689949474','456','Quan ly');
insert into nhanvien values ('kien3','cao','thai binh','01689949474','798','Nhan vien');
go
select * from nhanvien
go
insert into loaisanpham values ('1','Benh vien');
insert into loaisanpham values ('2','Truong hoc');
insert into loaisanpham values ('3','Nha o');
insert into loaisanpham values ('4','Khac');
go
insert into sanpham values ('1','Ban','1000','10','1');
insert into sanpham values ('2','Ghe','2000','15','1');
insert into sanpham values ('3','Cua','3000','20','2');
insert into sanpham values ('4','Tu','4000','25','3');
insert into sanpham values ('5','Quat tran','5000','30','2');
insert into sanpham values ('6','Quat ban','6000','25','4');
insert into sanpham values ('7','Tu dung','7000','20','3');
insert into sanpham values ('8','Tu nam','8000','15','2');
insert into sanpham values ('9','Chau su','90000','10','1');
insert into sanpham values ('10','Da hoa','10000','5','2');
go
insert into khachhang values ('Costumer','Bui Nhu Lac','0987654234','Ha Noi');
insert into khachhang values ('Costumer1','Bui Nhu Lac1','0987654234','Ha Noi');
insert into khachhang values ('Costumer2','Bui Nhu Lac2','0987654234','Ha Noi');
insert into khachhang values ('Costumer3','Bui Nhu Lac3','0987654234','Ha Noi');
go
insert into dathang values ('1','kien','Costumer','1/1/2011');
insert into dathang values ('2','kien3','Costumer2','2/1/2011');
insert into dathang values ('3','kien','Costumer3','3/1/2011');


